package com.ridbor001.demo.dao;

import com.ridbor001.demo.bean.RDBCourse;
import com.ridbor001.demo.util.RDBDataStore;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

public class RDBCourseDAO {
    RDBDataStore dataStore = new RDBDataStore();
    // 批量获取课程
    public Map<String, RDBCourse> getCoursesByIds(Set<String> courseIds) throws SQLException {
        if (courseIds == null || courseIds.isEmpty()) {
            return Collections.emptyMap();
        }

        String placeholders = courseIds.stream()
                .map(id -> "?")
                .collect(Collectors.joining(","));

        String sql = "SELECT * FROM courses WHERE course_id IN (" + placeholders + ")";
        Map<String, RDBCourse> courseMap = new HashMap<>();

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            int index = 1;
            for (String courseId : courseIds) {
                pstmt.setString(index++, courseId);
            }

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    RDBCourse course = new RDBCourse(
                            rs.getString("course_id"),
                            rs.getString("course_name"),
                            rs.getString("term"),
                            rs.getString("teacher_id")
                    );
                    courseMap.put(course.getCourseId(), course);
                }
            }
        }
        return courseMap;
    }

    // 根据学生获取课程列表
    public   List<RDBCourse> getCoursesByStudent(String studentId) throws SQLException {
        String sql = "SELECT c.course_id, c.course_name, c.term, c.teacher_id "
                + "FROM student_courses sc "
                + "JOIN courses c ON sc.course_id = c.course_id "
                + "WHERE sc.student_id = ?";

        List<RDBCourse> courses = new ArrayList<>();
        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, studentId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    courses.add(new RDBCourse(
                            rs.getString("course_id"),
                            rs.getString("course_name"),
                            rs.getString("term"),
                            rs.getString("teacher_id")
                    ));
                }
            }
        }
        return courses;
    }

    // 搜索课程
    public List<RDBCourse> searchCourses(String keyword) throws SQLException {
        String sql = "SELECT * FROM courses "
                + "WHERE course_id LIKE ? OR course_name LIKE ? OR teacher_id LIKE ? OR term LIKE ?";

        List<RDBCourse> courses = new ArrayList<>();
        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            String likeKeyword = "%" + keyword + "%";
            pstmt.setString(1, likeKeyword);
            pstmt.setString(2, likeKeyword);
            pstmt.setString(3, likeKeyword);
            pstmt.setString(4, likeKeyword);

            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    courses.add(new RDBCourse(
                            rs.getString("course_id"),
                            rs.getString("course_name"),
                            rs.getString("term"),
                            rs.getString("teacher_id")
                    ));
                }
            }
        }
        return courses;
    }
    public  boolean courseExists(String courseId) throws SQLException {
        final String sql = "SELECT COUNT(*) FROM courses WHERE course_id = ?";
        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, courseId);
            try (ResultSet rs = pstmt.executeQuery()) {
                return rs.next() && rs.getInt(1) > 0;
            }
        }
    }

    public RDBCourse findCourseById(String courseId) throws SQLException {
        final String sql = "SELECT course_id, course_name, term, teacher_id "
                + "FROM courses WHERE course_id = ?";

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, courseId);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return new RDBCourse(
                            rs.getString("course_id"),
                            rs.getString("course_name"),
                            rs.getString("term"),
                            rs.getString("teacher_id")
                    );
                }
            }
        }
        return null;
    }
    public   List<RDBCourse> getCoursesByTeacher(String teacherId) throws SQLException {
        final String sql = "SELECT course_id, course_name, term, teacher_id "
                + "FROM courses WHERE teacher_id = ?";

        List<RDBCourse> courses = new ArrayList<>();
        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, teacherId);
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    courses.add(new RDBCourse(
                            rs.getString("course_id"),
                            rs.getString("course_name"),
                            rs.getString("term"),
                            rs.getString("teacher_id")
                    ));
                }
            }
        }
        return courses;
    }
    public   boolean addCourse(RDBCourse course) throws SQLException {
        if (courseExists(course.getCourseId())) {
            return false;
        }

        final String sql = "INSERT INTO courses (course_id, course_name, term, teacher_id) "
                + "VALUES (?, ?, ?, ?)";

        try (Connection conn = dataStore.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, course.getCourseId());
            pstmt.setString(2, course.getCourseName());
            pstmt.setString(3, course.getSemester());
            pstmt.setString(4, course.getTeacherId());

            return pstmt.executeUpdate() > 0;
        }
    }

    public boolean deleteCourse(String courseId) throws SQLException {
        // 在事务中处理关联数据
        try (Connection conn = dataStore.getConnection()) {
            conn.setAutoCommit(false);

            // 检查是否有学生选课
            String checkSql = "SELECT COUNT(*) FROM student_courses WHERE course_id = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(checkSql)) {
                pstmt.setString(1, courseId);
                try (ResultSet rs = pstmt.executeQuery()) {
                    if (rs.next() && rs.getInt(1) > 0) {
                        conn.rollback();
                        return false;
                    }
                }
            }

            // 删除相关评教数据
            String deleteEvalsSql = "DELETE FROM evaluations WHERE course_id = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(deleteEvalsSql)) {
                pstmt.setString(1, courseId);
                pstmt.executeUpdate();
            }

            // 删除课程
            String deleteSql = "DELETE FROM courses WHERE course_id = ?";
            try (PreparedStatement pstmt = conn.prepareStatement(deleteSql)) {
                pstmt.setString(1, courseId);
                int rows = pstmt.executeUpdate();
                conn.commit();
                return rows > 0;
            } catch (SQLException e) {
                conn.rollback();
                throw e;
            }
        }
    }
}
